﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace University.Workshop.DataAccess
{
    public static class PurchaseReportData
    {
        const string ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=Pharmacy;Integrated Security=True";

        public static DataTable GetAllPurchaseProducts(string property)
        {
                string sqlSentence = "SELECT PU.Code, P.Name, PU.DatePurchase, PE.Name+' '+PE.Surname_1 as fullName, PU.TotalPurchase FROM Purchase PU, Provider P, Person PE WHERE PU.NitProvider=P.Nit and PE.Id=PU.CodeUser";
                SqlConnection cnn = null;
                SqlConnection tempCnn = null;
                DataTable table = new DataTable();
                try
                {
                    if (string.IsNullOrEmpty(property))
                    {
                        table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                    }
                    tempCnn = new SqlConnection(ConnectionString);
                    cnn = tempCnn;
                    cnn.Open();
                    SqlCommand cmd = tempCnn.CreateCommand();
                    cmd.CommandText = sqlSentence;
                    cmd.CommandType = System.Data.CommandType.Text;
                    SqlDataReader reader = cmd.ExecuteReader();
                    table.Load(reader);
                    return table;
                }
                finally
                {
                    if (table != null)
                    {
                        table.Dispose();
                    }
                    if (tempCnn != null)
                        tempCnn.Close();
                }
        }

        public static DataTable GetASpecificPurchaseDetail(string purchaseCode)
        {
            string sqlSentence = "SELECT PD.Quantity, PD.CodProduct, P.Name, PD.UnitPrice, PD.TotalPrice FROM PurchaseDetail PD, Product P WHERE PD.CodePurchase=@purchaseCode and PD.CodProduct=P.Code";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@purchaseCode", purchaseCode);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataTable GetAnSpecificProductBetweenFromTo(string productCode, DateTime from, DateTime to)
        {
            to = to.AddDays(1);
            string fromDataBase = from.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string toDataBase = to.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string sqlSentence = "SELECT PU.DatePurchase, PD.CodePurchase, P.Name, PD.Quantity, PD.UnitPrice, PD.TotalPrice FROM Purchase PU, PurchaseDetail PD, Provider P WHERE @productCode=PD.CodProduct and PU.Code=PD.CodePurchase and PU.NitProvider=P.Nit and PU.DatePurchase BETWEEN @dateFrom AND @dateTo";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@productCode", productCode);
                cmd.Parameters.AddWithValue("@dateFrom", fromDataBase);
                cmd.Parameters.AddWithValue("@dateTo", toDataBase);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static string GetProductNameByCode(string productCode)
        {
            string sqlSentence = "SELECT Name FROM Product WHERE Code=@productCode";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@productCode", productCode);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table.Rows[0].ItemArray[0].ToString();
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataTable GetAnSpecificProviderBetweenFromTo(string providerCode, DateTime from, DateTime to)
        {
            to = to.AddDays(1);
            string fromDataBase = from.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string toDataBase = to.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string sqlSentence = "SELECT PU.DatePurchase, PD.CodePurchase, P.Name, PD.Quantity, PD.UnitPrice, PD.TotalPrice FROM Purchase PU, PurchaseDetail PD, Product P WHERE @providerCode=PU.NitProvider and PU.Code=PD.CodePurchase and PD.CodProduct=P.Code and PU.DatePurchase BETWEEN @dateFrom AND @dateTo";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@providerCode", providerCode);
                cmd.Parameters.AddWithValue("@dateFrom", fromDataBase);
                cmd.Parameters.AddWithValue("@dateTo", toDataBase);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static string GetProviderNameByCode(string providerCode)
        {
            string sqlSentence = "SELECT Name FROM Provider WHERE Nit=@providerCode";

            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@providerCode", providerCode);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table.Rows[0].ItemArray[0].ToString();
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }

        public static DataTable GetAllPurchaseProductsBetweenDates(DateTime from, DateTime to)
        {
            to = to.AddDays(1);
            string fromDataBase = from.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + from.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string toDataBase = to.Year.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Month.ToString(System.Globalization.CultureInfo.CurrentCulture) + "-" + to.Day.ToString(System.Globalization.CultureInfo.CurrentCulture);
            string sqlSentence = "SELECT PU.Code, P.Name, PU.DatePurchase, PE.Name+' '+PE.Surname_1 as fullName, PU.TotalPurchase FROM Purchase PU, Provider P, Person PE WHERE PU.NitProvider=P.Nit and PE.Id=PU.CodeUser and PU.DatePurchase BETWEEN @dateFrom AND @dateTo";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable table = new DataTable();
            try
            {
                table.Locale = System.Globalization.CultureInfo.CurrentCulture;
                tempCnn = new SqlConnection(ConnectionString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@dateFrom", fromDataBase);
                cmd.Parameters.AddWithValue("@dateTo", toDataBase);
                SqlDataReader reader = cmd.ExecuteReader();
                table.Load(reader);
                return table;
            }
            finally
            {
                if (table != null)
                {
                    table.Dispose();
                }
                if (tempCnn != null)
                    tempCnn.Close();
            }
        }
    }
}
